package com.davidhigenbottam.HeatMap.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;

import com.davidhigenbottam.HeatMap.shared.Province;
import com.davidhigenbottam.HeatMap.shared.Query;

public abstract class EnrollmentRatioProvinceData extends ProvinceData {


	public abstract String getTableName();
	
	public EnrollmentRatioProvinceData(Query query) {
		super(query);
	}

	public void getProvincesFromDatabase() {

		Connection c = null;
		try {
			c = getConnection();
			getAllProvinces(c);

			String statement = "select province.id, province.name, ser "
					+ "from "+getTableName()+", province "
					+ "where "+getTableName()+".province_id= province.id "
					+ "and province.country_id=? "
					+ "and "+getTableName()+".year=? "
					+ "and "+getTableName()+".level_id=?";
			PreparedStatement stmt = c.prepareStatement(statement);
			stmt.setString(1, query.getCountryCode());
			stmt.setString(2, query.getYear());
			stmt.setString(3, query.getLevelOfEducation());
			if (stmt.execute()) {
				ResultSet rs = stmt.getResultSet();
				while (rs.next()) {
					String prov = rs.getString("name");
					double ser = rs.getDouble("ser");
					Province p = provinces.get(prov);
					p.setVal(ser);
					//log.info("prov:"+prov+" ser:"+ser);
				}
			}
		} catch (SQLException e) {
			log.log(Level.SEVERE, "SQLException", e);
			throw new RuntimeException(e);
		} finally {
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignore) {
				}
		}
	}	
	@Override
	public void getInstitutionsFromDatabase( Province p) {
		// do nothing not relevant for this data set
		
	}

}

